package com.sxfq.loansupermarket.backsystem.modular.marketing.service.impl;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerPublicService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.marketing.dao.ReservationsDao;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.QueryInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

@Service
@Transactional
public class ReservationsServiceImpl implements IReservationsService {
    @Autowired
    private ReservationsDao dao;

    @Autowired
    private SqlMapper sqlMapper;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    IConsultantService service;

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    ICustomerService customerService;

    /**
     * 建档  界面表格数据
     *
     * @param page
     * @param searchInfo
     * @return
     */
    public List<ReservationsInfo> selectPageListByStatusMap(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStatuss()) && !"请选择".equals(searchInfo.getStatuss())) {
            String statuss = "";
            String statuss2[] = searchInfo.getStatuss().split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            params.put("statuss", " t.statuss in ( '" + statuss + "') ");
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            if (searchInfo.getArchiving().indexOf(",") < 0) {
                String str = " ('" + searchInfo.getArchiving() + "') ";
                params.put("archiving", str);
            } else {
                String[] archivings = searchInfo.getArchiving().split(",");
                String str = " (";
                for (int i = 0; i < archivings.length; i++) {
                    if (i == archivings.length - 1) {
                        str += "'" + archivings[i] + "'";
                    } else {
                        str += "'" + archivings[i] + "',";
                    }
                }
                str += ") ";
                params.put("archiving", str);
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", " t.follower in (select t1.account from user t1 where t1.roleid='" + searchInfo.getDepartment() + "') ");
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName()) && !"请选择".equals(searchInfo.getOutpatientName())) {
            String outpatientName = "";
            String outpatientName2[] = searchInfo.getOutpatientName().split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            params.put("outpatientName", " t.outpatient_name in ('" + outpatientName + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        params.put("condition1", "order by t.filing_time desc");
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPages(page, params);
    }

    @Override
    public List<ReservationsInfo> selectPageListByStatusMap2(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStatuss()) && !"请选择".equals(searchInfo.getStatuss())) {
            String statuss = "";
            String statuss2[] = searchInfo.getStatuss().split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            params.put("statuss", " t.statuss in ( '" + statuss + "') ");
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            if (searchInfo.getArchiving().indexOf(",") < 0) {
                String str = " ('" + searchInfo.getArchiving() + "') ";
                params.put("archiving", str);
            } else {
                String[] archivings = searchInfo.getArchiving().split(",");
                String str = " (";
                for (int i = 0; i < archivings.length; i++) {
                    if (i == archivings.length - 1) {
                        str += "'" + archivings[i] + "'";
                    } else {
                        str += "'" + archivings[i] + "',";
                    }
                }
                str += ") ";
                params.put("archiving", str);
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", " t.follower in (select t1.account from user t1 where t1.roleid='" + searchInfo.getDepartment() + "') ");
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName()) && !"请选择".equals(searchInfo.getOutpatientName())) {
            String outpatientName = "";
            String outpatientName2[] = searchInfo.getOutpatientName().split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            params.put("outpatientName", " t.outpatient_name in ('" + outpatientName + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        params.put("condition1", "order by t.filing_time desc");
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPages2(page, params);
    }

    public int getQueryPagesCount(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss()) && !"请选择".equals(searchInfo.getStatuss())) {
            String statuss = "";
            String statuss2[] = searchInfo.getStatuss().split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            params.put("statuss", " t.statuss in ( '" + statuss + "') ");
        }

        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            if (searchInfo.getArchiving().indexOf(",") < 0) {
                String str = " ('" + searchInfo.getArchiving() + "') ";
                params.put("archiving", str);
            } else {
                String[] archivings = searchInfo.getArchiving().split(",");
                String str = " (";
                for (int i = 0; i < archivings.length; i++) {
                    if (i == archivings.length - 1) {
                        str += "'" + archivings[i] + "'";
                    } else {
                        str += "'" + archivings[i] + "',";
                    }
                }
                str += ") ";
                params.put("archiving", str);
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", " t.follower in (select t1.account from user t1 where  t1.roleid='" + searchInfo.getDepartment() + "') ");
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName()) && !"请选择".equals(searchInfo.getOutpatientName())) {
            String outpatientName = "";
            String outpatientName2[] = searchInfo.getOutpatientName().split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            params.put("outpatientName", " t.outpatient_name in ('" + outpatientName + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        params.put("condition1", "order by t.filing_time desc");
        return dao.getQueryPagesCount(page, params);
    }

    public int addIsRevisit(CustomerInfo info){
        String appointmentTime = "";
        if (info.getAppointmentTime() == null || info.getAppointmentTime() == "") {
            appointmentTime = "null";
        } else {
            appointmentTime = "'" + info.getAppointmentTime() + "'";
        }
        service.insertReturnVisit(info);
        String sqlselecthf = "select id,huifang,follower,archiving,filing_time,customer_name,customer_phone from reservations_info a where a.id='" + info.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlselecthf, ReservationsInfo.class);
        if (reservationsInfo != null) {
            if (reservationsInfo.getHuifang() == null || reservationsInfo.getHuifang() == "") {
                String updatesql = "update reservations_info a set a.statuss='" + info.getStatuss() + "', a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
                String updatesqlsc = "update reservations_infosc a set a.statuss='" + info.getStatuss() + "', a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesqlsc);
            } else {
                String updatesql = "update reservations_info a set a.statuss='" + info.getStatuss() + "', a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);

                String updatesqlsc = "update reservations_infosc a set a.statuss='" + info.getStatuss() + "', a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesqlsc);
            }
        }
        if (info.getFollower() == null || info.getFollower() == "") {
            String sql = "update reservations_info set " +
                    "reservations='" + info.getReservations() + "' ,appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                    " where id='" + info.getReservationsId() + "'";
            sqlMapper.update(sql);
            String sqlsc = "update reservations_infosc set  " +
                    "reservations='" + info.getReservations() + "' ,appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                    " where id='" + info.getReservationsId() + "'";
            return sqlMapper.update(sqlsc);
        } else {
            if (!(reservationsInfo.getFollower() + "").equals(info.getFollower() + "")) {
                //修改跟进人记录日志
                String sql2 = "INSERT INTO `reservations_updateFollower_info` (`update_people`, `update_time`, `update_cname`, " +
                        "`update_cphone`,`followery`,`followerx`,archiving,`filing_time`) VALUES " +
                        "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservationsInfo.getCustomerName()
                        + "', '" + reservationsInfo.getCustomerPhone() + "', '" + reservationsInfo.getFollower() + "','" + info.getFollower()
                        + "','" + reservationsInfo.getArchiving() + "', '" + reservationsInfo.getFilingTime() + "');";
                sqlMapper.insert(sql2);
            }
            String sqlsc = "update reservations_infosc set  " +
                    "reservations='" + info.getReservations() + "',follower='" + info.getFollower() + "',appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                    " where id='" + info.getReservationsId() + "'";
            sqlMapper.update(sqlsc);
            String sql = "update reservations_info set  " +
                    "reservations='" + info.getReservations() + "',follower='" + info.getFollower() + "',appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                    " where id='" + info.getReservationsId() + "'";
            return sqlMapper.update(sql);
        }
    }

    public int add_isRevisits(CustomerInfo info){
        String appointmentTime = "'" + info.getAppointmentTime() + "'";
        service.insertReturnVisit(info);
        String sqlselecthf = "select id,huifang,belonger,archiving,follower,filing_time,customer_phone,customer_name " +
                "from reservations_infosc a where a.id='" + info.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlselecthf, ReservationsInfo.class);
        /*if ("3".equals(info.getIsEffective())) {
            String updatesql = "update reservations_info a set a.follower='" + info.getFollowers() + "' where a.id='" + info.getReservationsId() + "'";
            String updatesqlsc = "update reservations_infosc a set a.follower='" + info.getFollowers() + "' where a.id='" + info.getReservationsId() + "'";
            sqlMapper.update(updatesql);
            sqlMapper.update(updatesqlsc);
        } else if ("4".equals(info.getIsEffective())) {
            String updatesql = "update reservations_info a set a.belonger='" + info.getBelongers() + "' where a.id='" + info.getReservationsId() + "'";
            String updatesqlsc = "update reservations_infosc a set a.belonger='" + info.getBelongers() + "' where a.id='" + info.getReservationsId() + "'";
            sqlMapper.update(updatesql);
            sqlMapper.update(updatesqlsc);
        }*/
        String isEffectiveSql = "";
        if ("1".equals(info.getIsEffective())) {
            isEffectiveSql += ",lasthuifangtime='" + DateTimeKit.now() + "'";
        }
        if (reservationsInfo != null) {
            if (reservationsInfo.getHuifang() == null || reservationsInfo.getHuifang() == "") {
                String updatesql = "update reservations_info a set a.follower='" + info.getFollowers() + "',a.belonger='"+info.getFollowers()+"', a.statuss='" + info.getStatuss() + "'," +
                        "a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "')) " + isEffectiveSql + ",appointment_time=" + appointmentTime + "," +
                        "return_to_remark='" + ShiroKit.getUser().getAccount() + "' where id='" + info.getReservationsId() + "'";
                String updatesqlsc = "update reservations_infosc a set a.follower='" + info.getFollowers() + "',a.belonger='"+info.getFollowers()+"', a.statuss='" + info.getStatuss() + "'," +
                        " a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" +
                        " " + info.getRevisitContent() + "')) " + isEffectiveSql + ",appointment_time=" + appointmentTime + "," +
                        "return_to_remark='" + ShiroKit.getUser().getAccount() + "' where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
                sqlMapper.update(updatesqlsc);
            } else {
                String updatesql = "update reservations_info a set a.follower='" + info.getFollowers() + "',a.belonger='"+info.getFollowers()+"', a.statuss='" + info.getStatuss() + "'," +
                        " a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" +
                        " " + info.getRevisitContent() + "')) " + isEffectiveSql + ",appointment_time=" + appointmentTime + "," +
                        "return_to_remark='" + ShiroKit.getUser().getAccount() + "' where id='" + info.getReservationsId() + "'";
                String updatesqlsc = "update reservations_infosc a set a.follower='" + info.getFollowers() + "',a.belonger='"+info.getFollowers()+"', a.statuss='" + info.getStatuss() + "'," +
                        " a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" +
                        " " + info.getRevisitContent() + "')) " + isEffectiveSql + ",appointment_time=" + appointmentTime + "," +
                        "return_to_remark='" + ShiroKit.getUser().getAccount() + "' where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
                sqlMapper.update(updatesqlsc);
            }
        } else {
            String sqlselec2 = "select id,huifang,belonger,archiving,follower,filing_time,customer_phone,customer_name " +
                    "from reservations_info a where a.id='" + info.getReservationsId() + "' limit 0,1";
            reservationsInfo = sqlMapper.selectOne(sqlselec2, ReservationsInfo.class);
            if (reservationsInfo.getHuifang() == null || reservationsInfo.getHuifang() == "") {
                String updatesql = "update reservations_info a set a.follower='" + info.getFollowers() + "',a.belonger='"+info.getFollowers()+"', a.statuss='" + info.getStatuss() + "'," +
                        "a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "')) " + isEffectiveSql + ",appointment_time=" + appointmentTime + "," +
                        "return_to_remark='" + ShiroKit.getUser().getAccount() + "' where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
            } else {
                String updatesql = "update reservations_info a set a.follower='" + info.getFollowers() + "',a.belonger='"+info.getFollowers()+"', a.statuss='" + info.getStatuss() + "'," +
                        " a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" +
                        " " + info.getRevisitContent() + "')) " + isEffectiveSql + ",appointment_time=" + appointmentTime + "," +
                        "return_to_remark='" + ShiroKit.getUser().getAccount() + "' where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
            }
        }
        if (!(reservationsInfo.getFollower() + "").equals(info.getFollowers() + "")) {
            //修改跟进人记录日志
            String sql2 = "INSERT INTO `reservations_updateFollower_info` (`update_people`, `update_time`, `update_cname`, " +
                    "`update_cphone`,`followery`,`followerx`,archiving,`filing_time`) VALUES " +
                    "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservationsInfo.getCustomerName()
                    + "', '" + reservationsInfo.getCustomerPhone() + "', '" + reservationsInfo.getFollower() + "','" + info.getFollowers()
                    + "','" + reservationsInfo.getArchiving() + "', '" + reservationsInfo.getFilingTime() + "');";
            sqlMapper.insert(sql2);
            //修改归属人记录日志
            String sql22 = "INSERT INTO `reservations_updateBelonger_info` (`update_people`, `update_time`, `update_cname`, " +
                    "`update_cphone`,`belongery`,`belongerx`,archiving,`filing_time`) VALUES " +
                    "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservationsInfo.getCustomerName()
                    + "', '" + reservationsInfo.getCustomerPhone() + "', '" + reservationsInfo.getFollower() + "','" + info.getFollowers()
                    + "','" + reservationsInfo.getArchiving() + "', '" + reservationsInfo.getFilingTime() + "');";
            sqlMapper.insert(sql22);
        }
        return 1;
    }

    /**
     * 已到记录  界面表格数据
     */
    public List<ReservationsInfo> selectPageListByStatusMapYDjl(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            if (searchInfo.getArchiving().indexOf(",") < 0) {
                String str = " ('" + searchInfo.getArchiving() + "') ";
                params.put("archiving", str);
            } else {
                String[] archivings = searchInfo.getArchiving().split(",");
                String str = " (";
                for (int i = 0; i < archivings.length; i++) {
                    if (i == archivings.length - 1) {
                        str += "'" + archivings[i] + "'";
                    } else {
                        str += "'" + archivings[i] + "',";
                    }
                }
                str += ") ";
                params.put("archiving", str);
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPagesYDjl(page, params);
    }


    public int getQueryPagesCountYDjl(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        return dao.getQueryPagesCountYDjl(page, params);
    }

    public ReservationsInfo selectOneById(String reservationsId) {
        String sql = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,left(a.appointment_arrival_time,19) as appointmentArrivalTime,left(a.filing_time,19) as filingTime," +
                "a.* from reservations_infosc a where id= '" + reservationsId + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
        return reservationsInfo;
    }

    public ReservationsInfo selectOneById2(String reservationsId) {
        String sql = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,a.* from reservations_info a where id= '" + reservationsId + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
        return reservationsInfo;
    }

    public int reservationsUpdate(ReservationsInfo reservationsInfo) {
        String filingTime = "";
        if (reservationsInfo.getFilingTime() == null || reservationsInfo.getFilingTime() == "") {
            filingTime = "null";
        } else {
            filingTime = "'" + reservationsInfo.getFilingTime() + "'";
        }
        String sql = "INSERT INTO `reservations_update_info` (`update_people`, `update_time`, `update_cname`, `update_cphone`,`archiving`,`filing_time`) VALUES " +
                "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservationsInfo.getCustomerName() + "', '" + reservationsInfo.getCustomerPhone() + "', '" + reservationsInfo.getArchiving() + "', " + filingTime + ");";
        return sqlMapper.insert(sql);
    }

    public List<ReservationsInfo> reservationsExport(SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStatuss()) && !"请选择".equals(searchInfo.getStatuss())) {
            String statuss = "";
            String statuss2[] = searchInfo.getStatuss().split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            params.put("statuss", " t.statuss in ( '" + statuss + "') ");
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            if (searchInfo.getArchiving().indexOf(",") < 0) {
                String str = " ('" + searchInfo.getArchiving() + "') ";
                params.put("archiving", str);
            } else {
                String[] archivings = searchInfo.getArchiving().split(",");
                String str = " (";
                for (int i = 0; i < archivings.length; i++) {
                    if (i == archivings.length - 1) {
                        str += "'" + archivings[i] + "'";
                    } else {
                        str += "'" + archivings[i] + "',";
                    }
                }
                str += ") ";
                params.put("archiving", str);
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", " t.follower in (select t1.account from user t1 where t1.roleid='" + searchInfo.getDepartment() + "') ");
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName()) && !"请选择".equals(searchInfo.getOutpatientName())) {
            String outpatientName = "";
            String outpatientName2[] = searchInfo.getOutpatientName().split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            params.put("outpatientName", " t.outpatient_name in ('" + outpatientName + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        params.put("condition1", "order by t.filing_time desc");
        return dao.getQueryPageExport(params);
    }

    //已到记录导出
    public List<ReservationsInfo> reservationsExportYDjl(SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArea())) {
            String area = "";
            String area2[] = searchInfo.getArea().split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            params.put("area", " t.outpatient_name  in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "'))");
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else if ("2".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        return dao.getQueryPageExportYDjl(params);
    }

    public int insert(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        info.setStatus("预约");
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getContent()) || info.getContent() == null) {
            info.setContent("");
        }
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"RoleName",0) != null) {
            info.setDepartment(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"RoleName",0)+"");
            info.setDepartment(info.getOneChannel());
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        if ("null".equals(info.getMaritalStatus()) || info.getMaritalStatus() == null) {
            info.setMaritalStatus(0);
        }
        if ("null".equals(info.getCustomerPhone2()) || info.getCustomerPhone2() == null) {
            info.setCustomerPhone2("");
        }
        if ("null".equals(info.getQqNumber()) || info.getQqNumber() == null) {
            info.setQqNumber("");
        }
        if ("null".equals(info.getWechatNumber()) || info.getWechatNumber() == null) {
            info.setWechatNumber("");
        }
        if ("null".equals(info.getKeyWord()) || info.getKeyWord() == null) {
            info.setKeyWord("");
        }
        if ("null".equals(info.getAddress()) || info.getAddress() == null) {
            info.setAddress("");
        }
        if ("null".equals(info.getTcName()) || info.getTcName() == null) {
            info.setTcName("");
        }
        if ("null".equals(info.getDoctor()) || info.getDoctor() == null) {
            info.setDoctor("");
        }
        if ("null".equals(info.getEconomicAbility()) || info.getEconomicAbility() == null) {
            info.setEconomicAbility("");
        }
        if ("null".equals(info.getOccupation()) || info.getOccupation() == null) {
            info.setOccupation("");
        }
        if ("null".equals(info.getConsumAbility()) || info.getConsumAbility() == null) {
            info.setConsumAbility("");
        }
        if ("null".equals(info.getConsumptionHabits()) || info.getConsumptionHabits() == null) {
            info.setConsumptionHabits("");
        }
        if ("null".equals(info.getIsHaveChildren()) || info.getIsHaveChildren() == null) {
            info.setIsHaveChildren("");
        }
        if ("null".equals(info.getCharacters()) || info.getCharacters() == null) {
            info.setCharacters("");
        }
        if ("null".equals(info.getObstaclePoint()) || info.getObstaclePoint() == null) {
            info.setObstaclePoint("");
        }
        CustomerInfo infoc=new CustomerInfo();
        infoc.setCustomerSex(info.getCustomerSex());
        infoc.setCustomerAge(info.getCustomerAge());
        String sql = "INSERT INTO reservations_info (`id`,`key_word`,`content`,`department`,`reservations_num`,`appointment_time`,`huifang`,`access_disease`," +
                "`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`," +
                " `customer_age`, `economic_ability`, `qq_number`,`wechat_number`, `other_contact`," +
                " `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`," +
                " `obstacle_point`, `one_channel`,`two_channel`,`thr_channel`, `one_medium`,`two_medium`,`thr_medium`, `is_large`," +
                " `remarks`, `classification`, `status`,`statuss`,`appointment_arrival_time`,`is_supplement`, `follower`, `belonger`," +
                "filling_status,consumption_habits,consum_ability,is_have_children,characters,address,marital_status,customer_phone2,szm," +
                "tc_name,doctor,search_field,customer_sex_head) VALUES" +
                "('" + info.getId() + "','" + info.getKeyWord() + "','" + info.getContent() + "','" + info.getDepartment() + "','" + info.getReservationsNum() + "'," + appointmentArrivalTime + ",'" + info.getHuifang() + "','" + info.getAccessDisease() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getOutpatientName() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getEconomicAbility() + "','" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'" + info.getObstaclePoint() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "'," + info.getIsLarge() + "," +
                "'" + info.getRemarks() + "','" + info.getClassification() + "','预约','" + info.getStatuss() + "'," + appointmentArrivalTime + ",'否','"
                + info.getArchiving() + "','" + info.getArchiving() + "','预约沉淀期','" + info.getConsumptionHabits() + "','"
                + info.getConsumAbility() + "','" + info.getIsHaveChildren() + "','" + info.getCharacters() + "','" + info.getAddress() + "','"
                + info.getMaritalStatus() + "','" + info.getCustomerPhone2() + "',firstconcats('" + info.getCustomerName() + "'),'"
                + info.getTcName() + "','" + info.getDoctor() + "'," +
                "concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                + customerPublicService.getSexPt(infoc)+"');";
        return sqlMapper.insert(sql);
    }

    //添加到市场预约表
    public int insertsc(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        info.setStatus("预约");
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getContent()) || info.getContent() == null) {
            info.setContent("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"RoleName",0) != null) {
            info.setDepartment(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"RoleName",0)+"");
            info.setDepartment(info.getOneChannel());
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        CustomerInfo infoc=new CustomerInfo();
        infoc.setCustomerSex(info.getCustomerSex());
        infoc.setCustomerAge(info.getCustomerAge());
        String sql = "INSERT INTO reservations_infosc (`id`,`key_word`,`content`,`department`,`reservations_num`,`appointment_time`,`huifang`,`access_disease`," +
                "`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`," +
                " `customer_age`, `economic_ability`, `qq_number`,`wechat_number`, `other_contact`," +
                " `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`," +
                " `obstacle_point`, `one_channel`,`two_channel`,`thr_channel`, `one_medium`,`two_medium`,`thr_medium`, `is_large`," +
                " `remarks`, `classification`, `status`,`statuss`,`appointment_arrival_time`,`is_supplement`, `follower`, `belonger`," +
                "filling_status,consumption_habits,consum_ability,is_have_children,characters,address,marital_status,customer_phone2,szm," +
                "tc_name,doctor,search_field,customer_sex_head) VALUES" +
                "('" + info.getId() + "','" + info.getKeyWord() + "','" + info.getContent() + "','" + info.getDepartment() + "','" + info.getReservationsNum() + "'," + appointmentArrivalTime + ",'" + info.getHuifang() + "','" + info.getAccessDisease() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getOutpatientName() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getEconomicAbility() + "','" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'" + info.getObstaclePoint() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "'," + info.getIsLarge() + "," +
                "'" + info.getRemarks() + "','" + info.getClassification() + "','预约','" + info.getStatuss() + "'," + appointmentArrivalTime + ",'否','"
                + info.getArchiving() + "','" + info.getArchiving() + "','预约沉淀期','" + info.getConsumptionHabits() + "','"
                + info.getConsumAbility() + "','" + info.getIsHaveChildren() + "','" + info.getCharacters() + "','" + info.getAddress() + "','"
                + info.getMaritalStatus() + "','" + info.getCustomerPhone2() + "',firstconcats('" + info.getCustomerName() + "'),'"
                + info.getTcName() + "','" + info.getDoctor() + "'," +
                "concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                + customerPublicService.getSexPt(infoc)+"');";
        return sqlMapper.insert(sql);
    }

    public int insertCustomerStatus(CustomerInfo info) {
        String seqsql="select id from distinction_customer_status where abbreviation='"+redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+""+"' and status=2 and reservations_id='"+info.getReservationsId()+"' limit 1";
        QueryInfo queryInfo= sqlMapper.selectOne(seqsql,QueryInfo.class);
        if(queryInfo!=null){
            String desql="delete from distinction_customer_status where abbreviation='"+redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+""+"' and status=2 and reservations_id='"+info.getReservationsId()+"'";
            sqlMapper.delete(desql);
        }
        String sql = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                " values('" + info.getReservationsId() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone()
                + "','" + ShiroKit.getUser().getOutpatientName() + "','" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"" + "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "') ";
        return sqlMapper.insert(sql);
    }

    public int insert(CustomerInfo info) {
        info.setStatus("到店");
        info.setArrivalStore(ShiroKit.getUser().getOutpatientName());
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setDepartment(info.getOneChannel());
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getWechatNumber()) || info.getWechatNumber() == null) {
            info.setWechatNumber("");
        }
        if ("null".equals(info.getAppointmentPayService()) || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getFieldTc()) || info.getFieldTc() == null) {
            info.setFieldTc("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String sql = "INSERT INTO reservations_info (`id`,`huifang`,`department`,`appointment_time`,`tc_name`,`first_visit_date`,`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`, `customer_age`, `qq_number`," +
                " `wechat_number`,`other_contact`, `country`, `province`, `cities`, `region`, `quarters`," +
                " `marital_status`, `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`, " +
                " `one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`,`remarks`, `status`,`appointment_arrival_time`," +
                "`is_supplement`,identification_number,arrival_store,szm,search_field,customer_sex_head) VALUES" +
                "('" + info.getReservationsId() + "','" + info.getHuifang() + "','" + info.getDepartment() + "','" + DateTimeKit.now() + "','" + info.getFieldTc() + "','" + DateTimeKit.now() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getArrivalStore() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() + "','" + info.getProvince() + "','" + info.getCities() + "','" + info.getRegion() + "'," +
                "'" + info.getAddress() + "'," + info.getMaritalStatus() + ",'" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "','"
                + info.getRemarks() + "', '" + info.getStatus() + "','" + DateTimeKit.now() + "','否','" + info.getMedicalRecordNumber() + "','"
                + ShiroKit.getUser().getOutpatientName() + "',firstconcats('" + info.getCustomerName() + "')," +
                "concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                +customerPublicService.getSexPt(info)+"')";
        return sqlMapper.insert(sql);
    }

    public List<ReservationsInfo> getCustomerPhoneCounts(String customerPhone) {
        String sql = "select id from reservations_info where (customer_phone like '%" + customerPhone + "%' or customer_phone2 like '%" + customerPhone + "%' )" +
                " order by filing_time desc ;";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList;
    }

    //查询电话1是否存在
    public ReservationsInfo getCustomerPhoneCount(String customerPhone) {
        String sql = "select id from reservations_info where (customer_phone='" + customerPhone + "' or customer_phone2='" + customerPhone + "') limit 1;";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
        return reservationsInfo;
    }

    //查询电话2是否存在
    public ReservationsInfo getCustomerPhoneCount2(String customerPhone2) {
        if (StringUtil.isNotEmpty(customerPhone2)) {
            String sql = "select id from reservations_info where (customer_phone='" + customerPhone2 + "' or customer_phone2='" + customerPhone2 + "') limit 1;";
            ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
            return reservationsInfo;
        } else {
            ReservationsInfo reservationsInfo = null;
            return reservationsInfo;
        }
    }

    //保存修改,判断修改后的电话1和电话2是否已经存在
    public int getUpdatePhoneCount(ReservationsInfo info) {
        if (StringUtil.isNotEmpty(info.getCustomerPhone())) {
            String sql = "select customer_phone from reservations_info where id !='" + info.getId() + "'" +
                    " and (customer_phone='" + info.getCustomerPhone() + "' " +
                    "or customer_phone2='" + info.getCustomerPhone() + "' ) and phone_shar_ship='本人';";
            List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
            return infoList.size();
        } else {
            return 0;
        }
    }

    //保存修改,判断修改后的电话1和电话2是否已经存在
    public int getUpdatePhoneCount2(ReservationsInfo info) {
        if (StringUtil.isNotEmpty(info.getCustomerPhone2())) {
            String sql = "select customer_phone from reservations_info where id !='" + info.getId() + "'" +
                    " and (customer_phone='" + info.getCustomerPhone2() + "' " +
                    "or customer_phone2='" + info.getCustomerPhone2() + "' ) and phone_shar_ship='本人';";
            List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
            return infoList.size();
        } else {
            return 0;
        }
    }

    @Transactional
    public void deleteReservation(String id) {
        String sql = "delete from reservations_info where id = '" + id + "'";
        String sql2 = "delete from reservations_infosc where id = '" + id + "'";
        sqlMapper.delete(sql2);
        sqlMapper.delete(sql);
    }

    public int update(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setAppointmentArrivalTime(appointmentArrivalTime);
        info.setUpdatePeople(ShiroKit.getUser().getAccount());
        info.setUpdateTime(new Date());
        String sql2 = "";
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        CustomerInfo infoc=new CustomerInfo();
        infoc.setCustomerSex(info.getCustomerSex());
        infoc.setCustomerAge(info.getCustomerAge());
        String sql = "update reservations_info set  `reservations_num`='" + info.getReservationsNum() + "', `customer_name`='" + info.getCustomerName()
                + "', `customer_phone`='" + info.getCustomerPhone() + "', `customer_phone2`='" + info.getCustomerPhone2() + "', `customer_sex`='" + info.getCustomerSex() + "', `customer_age`="
                + info.getCustomerAge() + ", `economic_ability`='" + info.getEconomicAbility() + "', `qq_number`='" + info.getQqNumber() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`outpatient_name`='" + info.getOutpatientName() + "',`phone_shar_ship`='"
                + info.getPhoneSharShip() + "',`appointment_pay_service`='" + info.getAppointmentPayService() + "',  `wechat_number`='"
                + info.getWechatNumber() + "', access_disease='" + info.getAccessDisease() + "', `archiving`='" + info.getArchiving()
                + "', `update_people`='" + ShiroKit.getUser().getAccount() + "',`update_time`='" + DateTimeKit.now()
                + "',`department`='" + info.getDepartment() + "',`obstacle_point`='" + info.getObstaclePoint()
                + "', `one_medium`='" + info.getOneMedium() + "',`two_medium`='" + info.getTwoMedium() + "',`thr_medium`='" + info.getThrMedium()
                + "', `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "',`thr_channel`='" + info.getThrChannel()
                + "', `is_large`='" + info.getIsLarge() + "', `remarks`='" + info.getRemarks() + "'," + sql2 +
                " `content`='" + info.getContent() + "',`key_word`='" + info.getKeyWord() + "',occupation='"+info.getOccupation()+"'," +
                " `appointment_arrival_time`=" + appointmentArrivalTime + ",characters='"+info.getCharacters()+"',consumption_habits='"+info.getConsumptionHabits()+"', " +
                " belonger='" + info.getBelonger() + "',customer_sex_head='"+customerPublicService.getSexPt(infoc)+"'" +
                " where id='" + info.getId() + "'";
        return sqlMapper.update(sql);
    }

    public int updatesc(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "" + info.getAppointmentArrivalTime() + "";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setAppointmentArrivalTime(appointmentArrivalTime);
        info.setUpdatePeople(ShiroKit.getUser().getAccount());
        info.setUpdateTime(new Date());
        String sql2 = "";
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        CustomerInfo infoc=new CustomerInfo();
        infoc.setCustomerSex(info.getCustomerSex());
        infoc.setCustomerAge(info.getCustomerAge());
        String sql = "update reservations_infosc set  `reservations_num`='" + info.getReservationsNum() + "', `customer_name`='" + info.getCustomerName()
                + "', `customer_phone`='" + info.getCustomerPhone() + "', `customer_phone2`='" + info.getCustomerPhone2() + "', `customer_sex`='" + info.getCustomerSex() + "', `customer_age`="
                + info.getCustomerAge() + ", `economic_ability`='" + info.getEconomicAbility() + "', `qq_number`='" + info.getQqNumber() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`outpatient_name`='" + info.getOutpatientName() + "',`phone_shar_ship`='"
                + info.getPhoneSharShip() + "',`appointment_pay_service`='" + info.getAppointmentPayService() + "',  `wechat_number`='"
                + info.getWechatNumber() + "', access_disease='" + info.getAccessDisease() + "', `archiving`='" + info.getArchiving()
                + "', `update_people`='" + ShiroKit.getUser().getAccount() + "',`update_time`='" + DateTimeKit.now()
                + "',`department`='" + info.getDepartment() + "',`obstacle_point`='" + info.getObstaclePoint()
                + "', `one_medium`='" + info.getOneMedium() + "',`two_medium`='" + info.getTwoMedium() + "',`thr_medium`='" + info.getThrMedium()
                + "', `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "',`thr_channel`='" + info.getThrChannel()
                + "', `is_large`='" + info.getIsLarge() + "', `remarks`='" + info.getRemarks() + "'," + sql2 +
                " `content`='" + info.getContent() + "',`key_word`='" + info.getKeyWord() + "',occupation='"+info.getOccupation()+"'," +
                " `appointment_arrival_time`=" + appointmentArrivalTime + ",characters='"+info.getCharacters()+"',consumption_habits='"+info.getConsumptionHabits()+"', " +
                " belonger='" + info.getBelonger() + "',customer_sex_head='"+customerPublicService.getSexPt(infoc)+"'" +
                " where id='" + info.getId() + "'";
        return sqlMapper.update(sql);
    }

    public int adminUpdate(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setAppointmentArrivalTime(appointmentArrivalTime);
        info.setUpdatePeople(ShiroKit.getUser().getAccount());
        info.setUpdateTime(new Date());
        String sql = "update reservations_info set `customer_name`='" + info.getCustomerName() + "', `customer_phone`='" + info.getCustomerPhone() + "', `customer_sex`='" + info.getCustomerSex() + "', `customer_age`=" + info.getCustomerAge() + ", `economic_ability`='" + info.getEconomicAbility() + "', `qq_number`='" + info.getQqNumber() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`outpatient_name`='" + info.getOutpatientName() + "',`phone_shar_ship`='" + info.getPhoneSharShip() + "',`appointment_pay_service`='" + info.getAppointmentPayService() + "',  `wechat_number`='" + info.getWechatNumber() + "', `other_contact`='" + info.getOtherContact() + "', `country`='" + info.getCountry() + "', `province`='" + info.getProvince() + "', `cities`='" + info.getCities() + "', `region`='" + info.getRegion() + "', `quarters`='" + info.getQuarters() + "'," +
                " `marital_status`='" + info.getMaritalStatus() + "', `occupation`='" + info.getOccupation() + "', `archiving`='" + info.getArchiving() + "', `update_people`='" + ShiroKit.getUser().getAccount() + "',`update_time`='" + DateTimeKit.now() + "', `vehicle`='" + info.getVehicle() + "', " +
                "  access_disease='" + info.getAccessDisease() + "', `obstacle_point`='" + info.getObstaclePoint() + "', `one_medium`='" + info.getOneMedium() + "',`two_medium`='" + info.getTwoMedium() + "',`thr_medium`='" + info.getThrMedium() + "', `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "',`thr_channel`='" + info.getThrChannel() + "', `is_large`='" + info.getIsLarge() + "', `remarks`='" + info.getRemarks() + "', `classification`='" + info.getClassification() + "'," +
                " `appointment_arrival_time`=" + appointmentArrivalTime + ", `appointment_time`=" + appointmentArrivalTime + " where id='" + info.getId() + "'";
        String sql1 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " set `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "'," +
                "`thr_channel`='" + info.getThrChannel() + "',`customer_name`='" + info.getCustomerName() + "', " +
                "`customer_phone`='" + info.getCustomerPhone() + "', `customer_sex`='" + info.getCustomerSex() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`customer_age`=" + info.getCustomerAge() + " where reservations_id='" + info.getId() + "';";
        sqlMapper.update(sql1);
        return sqlMapper.update(sql);
    }

    public int insertDeleteLog(ReservationsInfo reservations) {
        String sql = "INSERT INTO `reservations_delete_info` (`delete_people`, `delete_time`, `delete_cname`, `delete_cphone`,`archiving`,`filing_time`) VALUES " +
                "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservations.getCustomerName() + "', " +
                "'" + reservations.getCustomerPhone() + "', '" + reservations.getArchiving() + "', '" + reservations.getFilingTime() + "');";
        return sqlMapper.delete(sql);
    }
}